databaseChangeLog:
  - changeSet:
      id:  001_init_schema
      author:  Nekrasov.VA
      changes:
      -  sql:
           sql:

              alter table if exists block_history drop constraint if exists fk_block_history_person;
              alter table if exists block_history drop constraint if exists fk_block_history_comment;
              alter table if exists block_history drop constraint if exists fk_block_history_post;
              alter table if exists cities drop constraint if exists fk_country;
              alter table if exists dialogs drop constraint if exists fk_dialog_first_person;
              alter table if exists dialogs drop constraint if exists fk_dialog_second_person;
              alter table if exists friendships drop constraint if exists fk_friendship_person_dst;
              alter table if exists friendships drop constraint if exists fk_friendship_person_src;
              alter table if exists likes drop constraint if exists fk_like_person;
              alter table if exists messages drop constraint if exists fk_messages_author;
              alter table if exists messages drop constraint if exists fk_messages_dialog;
              alter table if exists messages drop constraint if exists fk_messages_recipient;
              alter table if exists notifications drop constraint if exists fk_notification_person;
              alter table if exists persons drop constraint if exists fk_person_settings;
              alter table if exists post2tag drop constraint if exists fk_post2tag_post;
              alter table if exists post2tag drop constraint if exists fk_post2tag_tag;
              alter table if exists post_comments drop constraint if exists fk_comment_person;
              alter table if exists post_comments drop constraint if exists fk_comment_post;
              alter table if exists post_comments drop constraint if exists fk_comment_parent_id;
              alter table if exists post_files drop constraint if exists fk_file_post;
              alter table if exists posts drop constraint if exists fk_person;

              drop table if exists block_history cascade;
              drop table if exists captcha cascade;
              drop table if exists cities cascade;
              drop table if exists countries cascade;
              drop table if exists currencies cascade;
              drop table if exists dialogs cascade;
              drop table if exists friendships cascade;
              drop table if exists likes cascade;
              drop table if exists messages cascade;
              drop table if exists notifications cascade;
              drop table if exists person_settings cascade;
              drop table if exists persons cascade;
              drop table if exists post2tag cascade;
              drop table if exists post_comments cascade;
              drop table if exists post_files cascade;
              drop table if exists posts cascade;
              drop table if exists storage cascade;
              drop table if exists tags cascade;
              drop table if exists weather cascade;

              create table block_history (
                 id bigserial not null,
                 comment_text varchar(255),
                 time timestamp(6),
                 author_id bigint not null,
                 comment_id bigint not null,
                 post_id bigint not null,
                 primary key (id)
                 );

              create table captcha (
                 id bigserial not null,
                 code varchar(255),
                 secret_code varchar(255),
                 time varchar(255),
                 primary key (id)
                 );

              create table cities (
                 id bigserial not null,
                 code2 varchar(255),
                 international_name varchar(255),
                 lat numeric,
                 lng numeric,
                 name varchar(255),
                 open_weather_id bigint,
                 state varchar(255),
                 country_id bigint not null,
                 primary key (id)
                 );

              create table countries (
                 id bigserial not null,
                 code2 varchar(255),
                 full_name varchar(255),
                 international_name varchar(255),
                 name varchar(255),
                 primary key (id)
                 );

              create table currencies (
                 id bigserial not null,
                 name varchar(255),
                 price varchar(255),
                 update_time varchar(255),
                 primary key (id)
                 );

              create table dialogs (
                 id bigserial not null,
                 last_active_time timestamp(6),
                 last_message_id bigint,
                 first_person_id bigint not null,
                 second_person_id bigint not null,
                 primary key (id)
                 );

              create table friendships (
                 id bigserial not null,
                 sent_time timestamp(6) not null,
                 status_name varchar(255) not null,
                 dst_person_id bigint not null,
                 src_person_id bigint not null,
                 unique (src_person_id, dst_person_id),
                 primary key (id)
                 );

              create table likes (
                 id bigserial not null,
                 entity_id bigint not null,
                 time timestamp(6) not null,
                 type varchar(255) not null,
                 person_id bigint not null,
                 unique (person_id, type, entity_id),
                 primary key (id)
                 );

              create table messages (
                 id bigserial not null,
                 is_deleted boolean,
                 message_text text,
                 read_status varchar(255),
                 time timestamp(6),
                 author_id bigint not null,
                 dialog_id bigint not null,
                 recipient_id bigint not null,
                 primary key (id)
                );

              create table notifications (
                 id bigserial not null,
                 contact varchar(255),
                 entity_id bigint,
                 is_read boolean,
                 notification_type varchar(255),
                 sent_time timestamp(6),
                 person_id bigint not null,
                 primary key (id)
                );

              create table person_settings (
                 id bigserial not null,
                 comment_comment boolean,
                 friend_birthday boolean,
                 friend_request boolean,
                 message boolean,
                 post boolean,
                 post_comment boolean,
                 post_like boolean,
                 primary key (id)
                );

              create table persons (
                 id bigserial not null,
                 first_name varchar(255),
                 last_name varchar(255),
                 e_mail varchar(255),
                 phone varchar(255),
                 birth_date timestamp(6),
                 about varchar(255),
                 change_password_token varchar(255),
                 city varchar(255),
                 configuration_code integer,
                 country varchar(255),
                 is_approved boolean,
                 is_blocked boolean,
                 is_deleted boolean,
                 deleted_time timestamp(6),
                 last_online_time timestamp(6),
                 message_permissions varchar(255) not null,
                 notifications_session_id varchar(255),
                 online_status boolean not null,
                 password varchar(255),
                 photo varchar(255),
                 reg_date timestamp(6),
                 telegram_id bigint,
                 person_settings_id bigint not null,
                 unique (e_mail),
                 primary key (id)
                );

              create table post2tag (
                 id bigserial not null,
                 post_id bigint not null,
                 tag_id bigint not null,
                 primary key (id)
                );

              create table post_comments (
                 id bigserial not null,
                 comment_text text not null,
                 is_blocked boolean not null,
                 is_deleted boolean not null,
                 time timestamp(6) not null,
                 author_id bigint not null,
                 post_id bigint not null,
                 parent_id bigint,
                 primary key (id)
                );

              create table post_files (
                 id bigserial not null,
                 name varchar(255),
                 path varchar(255),
                 post_id bigint not null,
                 primary key (id)
                );

              create table posts (
                 id bigserial not null,
                 title varchar(255) not null,
                 post_text text not null,
                 time timestamp(6) not null,
                 is_blocked boolean not null,
                 is_deleted boolean not null,
                 time_delete timestamp(6),
                 author_id bigint not null,
                 primary key (id)
                );

              create table storage (
                 id bigserial not null,
                 created_at timestamp(6),
                 file_name varchar(255),
                 file_size bigint,
                 file_type varchar(255),
                 owner_id bigint,
                 primary key (id)
                );

              create table tags (
                 id bigserial not null,
                 tag varchar(255) not null,
                 unique (tag),
                 primary key (id)
                );

              create table weather (
                id bigserial not null,
                city varchar(255) not null,
                clouds varchar(255),
                date timestamp(6) not null,
                open_weather_id bigint,
                unique (city),
                primary key (id)
               );

              alter table if exists block_history add constraint fk_block_history_person foreign key (author_id) references persons;
              alter table if exists block_history add constraint fk_block_history_comment foreign key (comment_id) references post_comments;
              alter table if exists block_history add constraint fk_block_history_post foreign key (post_id) references posts;
              alter table if exists cities add constraint fk_country foreign key (country_id) references countries;
              alter table if exists dialogs add constraint fk_dialog_first_person foreign key (first_person_id) references persons;
              alter table if exists dialogs add constraint fk_dialog_second_person foreign key (second_person_id) references persons;
              alter table if exists friendships add constraint fk_friendship_person_dst foreign key (dst_person_id) references persons;
              alter table if exists friendships add constraint fk_friendship_person_src foreign key (src_person_id) references persons;
              alter table if exists likes add constraint fk_like_person foreign key (person_id) references persons on delete cascade;
              alter table if exists messages add constraint fk_messages_author foreign key (author_id) references persons;
              alter table if exists messages add constraint fk_messages_dialog foreign key (dialog_id) references dialogs;
              alter table if exists messages add constraint fk_messages_recipient foreign key (recipient_id) references persons;
              alter table if exists notifications add constraint fk_notification_person foreign key (person_id) references persons;
              alter table if exists persons add constraint fk_person_settings foreign key (person_settings_id) references person_settings;
              alter table if exists post2tag add constraint fk_post2tag_post foreign key (post_id) references posts on delete cascade;
              alter table if exists post2tag add constraint fk_post2tag_tag foreign key (tag_id) references tags on delete cascade;
              alter table if exists post_comments add constraint fk_comment_person foreign key (author_id) references persons;
              alter table if exists post_comments add constraint fk_comment_post foreign key (post_id) references posts;
              alter table if exists post_comments add constraint fk_comment_parent_id foreign key (parent_id) references post_comments;
              alter table if exists post_files add constraint fk_file_post foreign key (post_id) references posts;
              alter table if exists posts add constraint fk_person foreign key (author_id) references persons;

